package com.eascs.admin.o2o.collectRecords.dao;

import org.springframework.stereotype.Repository;

import com.eascs.admin.common.dao.BaseO2oDao;
import com.eascs.admin.o2o.collectRecords.entity.CollectRecordsVo;
import com.eascs.app.dao.PageResult;
import com.eascs.app.dao.SqlPageQuery;
import com.eascs.common.util.StringUtil;

@Repository
public class CollectRecordsDao extends BaseO2oDao{

	public PageResult<Object[]> getListByPage(CollectRecordsVo vo) {
		SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<>(Object[].class, vo.getPageSize(), vo.getPageIndex());
		StringBuffer querySql = new StringBuffer();
		querySql.append(" select t.* FROM ( "
+" (SELECT orr.id,	oll.loanapply_no, opr.province_code AS province_code, ppd.item_code AS loan_bank_code,	oll.bill_code AS bill_code,	olu.customer_name  AS loaner_name,	oll.operator_name  AS operator_name,	opr.province_name  AS province_name,	ppd.item_name  AS loan_bank,	orr.received_date AS this_payment_date ,	orr.received_amount,	orr.update_name AS update_name,	orr.coefficient,	'逾期还款'  "
+" FROM	o2o_received_records orr  "
+" LEFT JOIN o2o_loanapply oll ON oll.loanapply_no = orr.loanapply_no  "
+" LEFT JOIN o2o_loan_user olu ON oll.loanapply_no = olu.loanapply_no  "
+" LEFT JOIN o2o_bank_loan_data obld ON oll.bill_code = obld.bill_code  "
+" LEFT JOIN o2o_platform_regional opr ON oll.ltd = opr.platform_id  "
+" LEFT JOIN pub_dditem ppd ON obld.loan_bank = ppd.item_code  "
+" LEFT JOIN pub_datadictionary pdt ON ppd.dd_no = pdt.dd_no  "
+" WHERE	1=1  AND /**DATA_PERMISSION(oll.ltd)**/  olu.spouse_id IS NULL AND olu.sponsor_type IS NULL AND pdt.dd_code = 'BANKTYPEO2O' AND orr.is_del = 'N')  " 
+" UNION ALL  "
	+" (SELECT	obr.id,		obr.loanapply_no,	opr.province_code AS province_code, ppd.item_code AS loan_bank_code,	obr.bill_code   AS bill_code,		oeld.loaner_name   AS loaner_name,		oll.operator_name   AS operator_name,		opr.province_name   AS province_name,	ppd.item_name   AS loan_bank,  " 
+" DATE_FORMAT(			obr.this_payment_date,			'%Y-%m-%d'	) AS this_payment_date,	obr.this_payment_principal,		obr.liquidate_name   AS update_name,		1.5,		'代偿回款'  "
+" 	FROM	o2o_badloan_record obr  "
+" 	INNER JOIN o2o_ea_loan_data oeld ON obr.bill_code = oeld.bill_code_ea  "
+" 	LEFT JOIN o2o_platform_regional opr ON oeld.ltd = opr.platform_id  "
+" 	LEFT JOIN o2o_loanapply oll ON oll.bill_code = obr.bill_code  "
+" 	LEFT JOIN o2o_bank_loan_data obld ON oll.bill_code = obld.bill_code  "
+" 	LEFT JOIN pub_dditem ppd ON obld.loan_bank = ppd.item_code  "
+" 	LEFT JOIN pub_datadictionary pdt ON ppd.dd_no = pdt.dd_no  "
+" 	WHERE 1=1 AND /**DATA_PERMISSION(oll.ltd)**/ obr.is_delete = 'N' AND pdt.dd_code = 'BANKTYPEO2O')) t WHERE 1=1 ");
//		querySql.append(
//				" WHERE 1=1 AND /**DATA_PERMISSION(oll.ltd)**/ olu.spouse_id is null AND olu.sponsor_type is null AND pdt.dd_code = 'BANKTYPEO2O' AND orr.is_del='N' ");// 付款类型'ZD'是什么
		if (!StringUtil.isEmpty(vo.getBillCode())) {
			querySql.append(" AND t.bill_code like ? ");
			sqlPageQuery.addLikeParam(vo.getBillCode());
		}
		if (!StringUtil.isEmpty(vo.getLoanBank())) {//放款银行实际是依据银行code查询
			querySql.append(" AND t.loan_bank_code = ? ");
			sqlPageQuery.addParam(vo.getLoanBank());
		}
		if (!StringUtil.isEmpty(vo.getOperatorName())) {// 信贷经理
			querySql.append(" AND t.operator_name like ? ");
			sqlPageQuery.addLikeParam(vo.getOperatorName());
		}
		if (!StringUtil.isEmpty(vo.getLoanerName())) {//借款人
			querySql.append(" AND t.loaner_name like ? ");
			sqlPageQuery.addLikeParam(vo.getLoanerName());
		}
		if (!StringUtil.isEmpty(vo.getProvinceCode())) {//省区
			querySql.append(" AND t.province_code = ? ");
			sqlPageQuery.addParam(vo.getProvinceCode());
		}
//=================少一个清收人,待添加=====================
		if (!StringUtil.isEmpty(vo.getCollector())) {//清收人
			querySql.append(" AND t.update_name like ? ");
			sqlPageQuery.addLikeParam(vo.getCollector());
		}
//==============================-==========================	
		querySql.append(" order by t.this_payment_date desc ");
		// querySql.append(" order by orr.create_date desc ");
		sqlPageQuery.setSumColumnIndexs(new int[] { 10 });//放款金额统计、数据统计
		sqlPageQuery.setQuerySql(querySql.toString());
		return this.findDataWithPage(sqlPageQuery);
	}
	

}
